﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using SISCON.Dao;
using SISCON.Model;
using log4net;
using SISCON.Common;


namespace SISCON.Dao
{
    /// <summary>
    /// 
    /// </summary>
    public class ClassroomDAO : CommonDAO
    {

        /// <summary>
        /// Obtiene una lista con todos los cursos que se impartan en el aula especificada por classroomId
        /// </summary>
        /// <param name="classroomId">La clave del salon buscado</param>
        /// <returns></returns>
        public List<Course> GetClassroom(string classroomId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Course> lstCourses = new List<Course>();
            string classroomLocation = "";
            string classroomNumber = "";

            try
            {
                if (classroomId.IndexOf("-") >= 0)
                {
                    classroomLocation = classroomId.Split('-')[0];
                    classroomNumber = classroomId.Split('-')[1];

                    if (classroomId.Split('-').Length > 2)
                    {
                        classroomNumber += classroomId.Split('-')[2];
                    }

                }
                else if (classroomId.IndexOf("/") >= 0)
                {
                    classroomLocation = classroomId.Split('/')[0];
                    classroomNumber = classroomId.Split('/')[1];
                    if (classroomId.Split('/').Length > 2)
                    {
                        classroomNumber += classroomId.Split('/')[2];
                    }
                }
                else
                {
                    if (classroomId.Contains("OFNA"))
                    {
                        classroomLocation = classroomId.Substring(0, 4);
                        classroomNumber = classroomId.Substring(4, classroomId.Length - 4);
                    }
                    else if (classroomId.Contains("CDES"))
                    {
                        classroomLocation = classroomId.Substring(0, 4);
                        classroomNumber = classroomId.Substring(4, classroomId.Length - 4);
                    }
                    else if (classroomId.Contains("LAB"))
                    {
                        classroomLocation = classroomId.Substring(0, 3);
                        classroomNumber = classroomId.Substring(3, classroomId.Length - 3);
                    }
                    else
                    {
                        classroomLocation = classroomId.Substring(0, 2);
                        classroomNumber = classroomId.Substring(2, classroomId.Length - 2);
                    }
                }
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = "SELECT * FROM sis_course sc LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id";
                       sql += " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id";
                       sql += " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id"; 
                       sql += " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id";
                       sql += " WHERE sc.course_id IN";
                       sql += " (SELECT course_id FROM sis_course WHERE course_id IN";
                       sql += " (select course_id FROM sis_course_schedule WHERE schedule_id IN (";
                       sql += " select schedule_id from sis_schedule where classroom_location LIKE '@classroomLocation' AND classroom_number LIKE '@classroomNumber')))";


                       SqlCommand sqlCommand = new SqlCommand(sql, conn);
                       sqlCommand.Parameters.Add(getNewParameter("@classroomLocation", SqlDbType.NVarChar, 255, "%" + classroomLocation + "%"));
                       sqlCommand.Parameters.Add(getNewParameter("@classroomNumber", SqlDbType.NVarChar, 255, "%" + classroomNumber + "%"));

                       reader = sqlCommand.ExecuteReader();

                       Course course = null;
                       Teacher teacher = null;
                       Schedule schedule = null;

                       HashSet<string> teachersSet = null;
                       HashSet<string> scheduleSet = null;

                       while (reader.Read())
                       {
                           if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                           {
                               course = new Course();

                               course.CourseId = getReaderLong(reader, "course_id");
                               course.CourseCode = getReaderString(reader, "course_code");
                               course.CourseName = getReaderString(reader, "course_name");
                               course.CourseNumber = getReaderString(reader, "course_number");
                               course.IsActive = getReaderBool(reader, "is_active");

                               course.Teachers = new List<Teacher>();
                               course.Schedules = new List<Schedule>();

                               teachersSet = new HashSet<string>();
                               scheduleSet = new HashSet<string>();

                               lstCourses.Add(course);
                           }

                           if (course.IsActive)
                           {
                               if ((teacher == null || teacher.TeacherId != getReaderLong(reader, "teacher_id")) && !teachersSet.Contains(getReaderLong(reader, "teacher_id").ToString()))
                               {
                                   teacher = new Teacher();
                                   teacher.TeacherId = getReaderLong(reader, "teacher_id");
                                   teacher.SchoolId = getReaderString(reader, "school_id");
                                   teacher.Name = getReaderString(reader, "name");

                                   teachersSet.Add(teacher.TeacherId.ToString());
                                   course.Teachers.Add(teacher);
                               }

                               if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                               {
                                   schedule = new Schedule();
                                   schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                                   schedule.Days = getReaderCommaSeparatedString(reader, "days");
                                   schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                                   schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                                   schedule.Classroom = new Classroom()
                                   {
                                       ClassroomLocation = getReaderString(reader, "classroom_location"),
                                       ClassroomNumber = getReaderString(reader, "classroom_number")
                                   };

                                   scheduleSet.Add(schedule.ScheduleId.ToString());
                                   course.Schedules.Add(schedule);
                               }
                           }
                       }
                       reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;

        }

        /// <summary>
        /// Obtiene una lista con todos los cursos que se impartan en el aula especificada por classroomId
        /// </summary>
        /// <param name="classroomId">La clave del salon buscado</param>
        /// <returns></returns>
        public List<Course> GetClassroomCourses(string classroomId, long userId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;

            List<Course> lstCourses = new List<Course>();
            string classroomLocation = "";
            string classroomNumber = "";

            try
            {
                if (classroomId.IndexOf("-") >= 0)
                {
                    classroomLocation = classroomId.Split('-')[0];
                    classroomNumber = classroomId.Split('-')[1];

                    if (classroomId.Split('-').Length > 2)
                    {
                        classroomNumber += classroomId.Split('-')[2];
                    }

                }
                else if (classroomId.IndexOf("/") >= 0)
                {
                    classroomLocation = classroomId.Split('/')[0];
                    classroomNumber = classroomId.Split('/')[1];
                    if (classroomId.Split('/').Length > 2)
                    {
                        classroomNumber += classroomId.Split('/')[2];
                    }
                }
                else
                {
                    if (classroomId.Contains("OFNA"))
                    {
                        classroomLocation = classroomId.Substring(0, 4);
                        classroomNumber = classroomId.Substring(4, classroomId.Length - 4);
                    }
                    else if (classroomId.Contains("CDES"))
                    {
                        classroomLocation = classroomId.Substring(0, 4);
                        classroomNumber = classroomId.Substring(4, classroomId.Length - 4);
                    }
                    else if (classroomId.Contains("LAB"))
                    {
                        classroomLocation = classroomId.Substring(0, 3);
                        classroomNumber = classroomId.Substring(3, classroomId.Length - 3);
                    }
                    else
                    {
                        classroomLocation = classroomId.Substring(0, 2);
                        classroomNumber = classroomId.Substring(2, classroomId.Length - 2);
                    }
                }
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = "SELECT * FROM sis_course sc LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id";
                sql += " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id";
                sql += " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id";
                sql += " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id";
                sql += " WHERE sc.created_by = @userId AND sc.course_id IN";
                sql += " (SELECT course_id FROM sis_course WHERE course_id IN";
                sql += " (select course_id FROM sis_course_schedule WHERE schedule_id IN (";
                sql += " select schedule_id from sis_schedule where classroom_location LIKE @classroomLocation AND classroom_number LIKE @classroomNumber)))";

                SqlCommand sqlCommand = new SqlCommand(sql, conn);

                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Parameters.Add(getNewParameter("@classroomLocation", SqlDbType.NVarChar, 50, "%" + classroomLocation + "%"));
                sqlCommand.Parameters.Add(getNewParameter("@classroomNumber", SqlDbType.NVarChar, 50, "%" + classroomNumber + "%"));

                reader = sqlCommand.ExecuteReader();

                Course course = null;
                Teacher teacher = null;
                Schedule schedule = null;

                HashSet<string> teachersSet = null;
                HashSet<string> scheduleSet = null;

                while (reader.Read())
                {
                    if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                    {
                        course = new Course();

                        course.CourseId = getReaderLong(reader, "course_id");
                        course.CourseCode = getReaderString(reader, "course_code");
                        course.CourseName = getReaderString(reader, "course_name");
                        course.CourseNumber = getReaderString(reader, "course_number");
                        course.IsActive = getReaderBool(reader, "is_active");

                        course.Teachers = new List<Teacher>();
                        course.Schedules = new List<Schedule>();

                        teachersSet = new HashSet<string>();
                        scheduleSet = new HashSet<string>();

                        lstCourses.Add(course);
                    }

                    if (course.IsActive)
                    {
                        if ((teacher == null || teacher.TeacherId != getReaderLong(reader, "teacher_id")) && !teachersSet.Contains(getReaderLong(reader, "teacher_id").ToString()))
                        {
                            teacher = new Teacher();
                            teacher.TeacherId = getReaderLong(reader, "teacher_id");
                            teacher.SchoolId = getReaderString(reader, "school_id");
                            teacher.Name = getReaderString(reader, "name");

                            teachersSet.Add(teacher.TeacherId.ToString());
                            course.Teachers.Add(teacher);
                        }

                        if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                        {
                            schedule = new Schedule();
                            schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                            schedule.Days = getReaderCommaSeparatedString(reader, "days");
                            schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                            schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                            schedule.Classroom = new Classroom()
                            {
                                ClassroomLocation = getReaderString(reader, "classroom_location"),
                                ClassroomNumber = getReaderString(reader, "classroom_number")
                            };

                            scheduleSet.Add(schedule.ScheduleId.ToString());
                            course.Schedules.Add(schedule);
                        }
                    }
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;

        }

        /// <summary>
        /// Obtiene todas las aulas registradas en la base de datos
        /// </summary>
        /// <returns></returns>
        public List<Classroom> GetAllClassrooms()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            List<Classroom> classroomList = new List<Classroom>();
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = @"SELECT * FROM sis_classroom ORDER BY classroom_id asc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Classroom classroom = new Classroom();
                    classroom.ClassroomId = getReaderLong(reader, @"classroom_id");
                    classroom.ClassroomLocation = getReaderString(reader, @"classroom_location");
                    classroom.ClassroomNumber = getReaderString(reader, @"classroom_number");
                    classroomList.Add(classroom);

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return classroomList ;

        }

        /// <summary>
        /// Guarda una nueva aula dentro de la base de datos
        /// </summary>
        /// <param name="classroomId">El codigo del aula</param>
        /// <param name="classroomLocation">El edificio donde esta ubicada el aula</param>
        /// <param name="classroomNumber">El codigo interno del aula</param>
        public void SaveClassroom(string classroomId, string classroomLocation, string classroomNumber, long createdBy)
        {
            SqlConnection conn = null;
            try
            {
                if (classroomId != null && !classroomId.Equals("0"))
                {
                    conn = new SqlConnection(_ConnStr);
                    conn.Open();
                    string sqlQuery = @"UPDATE sis_classroom SET classroom_location = @classroomLocation, classroom_number = @classroomNumber, created_by = @createdBy WHERE classroom_id=@classroomId";
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.Parameters.Add(getNewParameter(@"classroomLocation", SqlDbType.NVarChar, 50, classroomLocation));
                    cmd.Parameters.Add(getNewParameter(@"classroomNumber", SqlDbType.NVarChar, 50, classroomNumber));
                    cmd.Parameters.Add(getNewParameter(@"createdBy", SqlDbType.BigInt, 0, createdBy));
                    cmd.Prepare();

                    cmd.ExecuteNonQuery();
                }
                else
                {
                    conn = new SqlConnection(_ConnStr);
                    conn.Open();
                    string sqlQuery = @"INSERT INTO sis_classroom(classroom_location, classroom_number, created_by) VALUES (@classroomLocation, @classroomNumber, @createdBy)";
                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
                    cmd.Parameters.Add(getNewParameter(@"classroomLocation", SqlDbType.NVarChar, 50, classroomLocation));
                    cmd.Parameters.Add(getNewParameter(@"classroomNumber", SqlDbType.NVarChar, 50, classroomNumber));
                    cmd.Parameters.Add(getNewParameter(@"createdBy", SqlDbType.BigInt, 0, createdBy));
                    cmd.Prepare();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre de usuario ya existe. Favor de verificar");
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch(Exception ex){
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Elimina un aula de la base de datos
        /// </summary>
        /// <param name="classroomId">Id del aula que se desea eliminar</param>
        public void DeleteClassroom(string classroomId)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sqlQuery = "DELETE FROM sis_classroom WHERE classroom_id=@classroomId";
                cmd = new SqlCommand(sqlQuery, conn);
                cmd.Parameters.Add(@"classroomId",SqlDbType.BigInt);
                cmd.Parameters[@"classroomId"].Value = long.Parse(classroomId);
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                    throw ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}